package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.GUID;
import edu.byu.intex2.data.bo.*;
import java.sql.*;

/**
 * PartServiceLine Data Access Object
 * @author Jason Whiting
 */
public class PartServiceLineDAO
{

    /////////////////////////////////////////////
    ///   Singleton code
    private static PartServiceLineDAO instance = null;

    /** Creates a new instance of PartServiceLineDAO */
    private PartServiceLineDAO()
    {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized PartServiceLineDAO getInstance()
    {
        if (instance == null)
        {
            instance = new PartServiceLineDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /**
     * Creates a new partServiceLine in the database
     * @param serviceId id of service
     * @param taskId id of task
     * @param partId id of part
     */
    public PartServiceLine create(ServiceLine sl, String partId) throws DataException
    {
        String id = sl.getServiceId() + sl.getTaskId() + partId;
        PartServiceLine partServiceLine = new PartServiceLine(id);
        partServiceLine.setObjectAlreadyInDB(false);
        partServiceLine.setServiceId(sl.getServiceId());
        partServiceLine.setTaskId(sl.getTaskId());
        partServiceLine.setServiceLine(sl);
        partServiceLine.setPartId(partId);
        try
        {
            partServiceLine.setPsf(PartsAtServiceFacilityDAO.getInstance().read(sl.getService().getServiceFacilityId(), partId));
        } catch (Exception e)
        {
            e.printStackTrace();
        }

        Cache.getInstance().put(partServiceLine.getId(), partServiceLine); // put in cache just in case...
        return partServiceLine;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /**
     * Reads an existing partServiceLine from the database     */
    public PartServiceLine read(String serviceId, String taskId, String partId) throws DataException
    {
        // compound GUID
        String id = serviceId + taskId + partId;
        if (Cache.getInstance().containsKey(id))
        {
            return (PartServiceLine) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try
        {
            return read(serviceId, taskId, partId, conn);
        } catch (Exception e)
        {
            throw new DataException("An error occurred while reading the PartServiceLine object information.", e);
        } finally
        {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /**
     * Internal method to read an existing partServiceLine from the database
     * @return partServiceLine
     */
    synchronized PartServiceLine read(String serviceId, String taskId, String partId, Connection conn) throws Exception
    {
        String id = serviceId + taskId + partId;
        if (Cache.getInstance().containsKey(id))
        {
            return (PartServiceLine) Cache.getInstance().get(id);
        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM part_service_line where service_id=? AND task_id=? and part_id=?");

        try
        {
            stmt.setString(1, serviceId);
            stmt.setString(2, taskId);
            stmt.setString(3, partId);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next())
            {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("PartServiceLine with serviceId '" + serviceId + "' and taskId '" + taskId + "' and partId '" + partId + "' not found.");
        } finally
        {
            stmt.close();
        }
    }//read

    /** Internal method to create a partServiceLine object from a record  */
    synchronized PartServiceLine readRecord(ResultSet rs, Connection conn) throws Exception
    {
        String id = rs.getString("service_id") + rs.getString("task_id") + rs.getString("part_id");
        if (Cache.getInstance().containsKey(id))
        {
            return (PartServiceLine) Cache.getInstance().get(id);
        }
        // Create new partServiceLine object
        PartServiceLine partServiceLine = new PartServiceLine(id);

        // PartServiceLine is already in DB
        partServiceLine.setObjectAlreadyInDB(true);

        // Put partServiceLine in cache
        Cache.getInstance().put(partServiceLine.getId(), partServiceLine);

        // Set PartServiceLine Attributes
        partServiceLine.setServiceId(rs.getString("service_id"));
        partServiceLine.setTaskId(rs.getString("task_id"));
        partServiceLine.setPartId(rs.getString("part_id"));
        partServiceLine.setQuantityUsed(rs.getInt("quantity_used"));

        // PartServiceLine is dirty and needs to be saved to DB
        partServiceLine.setDirty(false);

        return partServiceLine;
    }//readRecord


    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing partServiceLine in the database */
    public void save(PartServiceLine partServiceLine) throws DataException
    {
        // get a connection from the pool, save all of the services first using the other partServiceLine method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try
        {
            save(partServiceLine, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e)
        {
            try
            {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2)
            {
                throw new DataException("Could not roll back the partServiceLine database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the partServiceLine object information.", e);
        } finally
        {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a partServiceLine in the database */
    void save(PartServiceLine partServiceLine, Connection conn) throws Exception
    {
        // put the partServiceLine object to cache
        Cache.getInstance().put(partServiceLine.getId(), partServiceLine);

        // decide whether to update or insert into the database
        if (partServiceLine.isObjectAlreadyInDB() && partServiceLine.isDirty())
        {
            update(partServiceLine, conn);
        } else if (partServiceLine.isDirty())
        {
            insert(partServiceLine, conn);
        }//if
    }

    /** External method to update or create a partServiceLine in the database without committing connection */
    public void saveNoCommit(PartServiceLine partServiceLine, Connection conn) throws Exception
    {
        // Get Already in Databsae
        Boolean alreadyInDB = partServiceLine.isObjectAlreadyInDB();

        // decide whether to update or insert into the database
        if (alreadyInDB)
        {
            update(partServiceLine, conn);
        } else
        {
            insert(partServiceLine, conn);
        }

        int newQuantity = partServiceLine.getPsf().getQuantity() - partServiceLine.getQuantityUsed();
        partServiceLine.getPsf().setQuantity(newQuantity);
        PartsAtServiceFacilityDAO.getInstance().saveNoCommit(partServiceLine.getPsf(), conn);
    }

    /** Saves an existing partServiceLine to the database */
    private void update(PartServiceLine partServiceLine, Connection conn) throws Exception
    {
        PreparedStatement stmt = conn.prepareStatement("UPDATE part_service_line SET quantity_used=? WHERE service_id=? AND task_id=? and part_id=?");
        try
        {
            stmt.setInt(1, partServiceLine.getQuantityUsed());
            stmt.setString(2, partServiceLine.getServiceId());
            stmt.setString(3, partServiceLine.getTaskId());
            stmt.setString(4, partServiceLine.getPartId());
            stmt.execute();

            partServiceLine.setDirty(false);
        } finally
        {
            stmt.close();
        }
    }

    /** Inserts a new partServiceLine into the database */
    private void insert(PartServiceLine partServiceLine, Connection conn) throws Exception
    {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO part_service_line (service_id, task_id, part_id, quantity_used ) VALUES (?, ?, ?, ?)");
        try
        {
            stmt.setString(1, partServiceLine.getServiceId());
            stmt.setString(2, partServiceLine.getTaskId());
            stmt.setString(3, partServiceLine.getPartId());
            stmt.setInt(4, partServiceLine.getQuantityUsed());
            stmt.execute();

            partServiceLine.setObjectAlreadyInDB(true);
            partServiceLine.setDirty(false);
        } catch (Exception e)
        {
            e.printStackTrace();
        } finally
        {
            stmt.close();
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(PartServiceLine partServiceLine) throws DataException
    {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }
}
